package com.topisv.tms.service.occupiedArea;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.apache.commons.lang.StringUtils;
import org.apache.shiro.SecurityUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.topisv.tms.entity.ProcessLog;
import com.topisv.tms.entity.occupiedArea.OarRecountPlan;
import com.topisv.tms.entity.occupiedArea.OccupiedAreaConfig;
import com.topisv.tms.entity.occupiedArea.OccupiedAreaResult;
import com.topisv.tms.repository.ProcessLogDao;
import com.topisv.tms.repository.occupiedArea.OarRecountPlanDao;
import com.topisv.tms.repository.occupiedArea.OccupiedAreaConfigDao;
import com.topisv.tms.repository.occupiedArea.OccupiedAreaResultDao;
import com.topisv.tms.service.account.ShiroDbRealm.ShiroUser;

/**
 * 仓库占用面积配置_service
 * 
 * @author gh
 * 
 */
@Component
@Transactional(readOnly = true)
public class OccupiedAreaConfigService {

	@Autowired
	private OccupiedAreaConfigDao oacDao;
	@Autowired
	private OarRecountPlanService orpService;
	@Autowired
	private OccupiedAreaResultDao oarDao;
	@Autowired
	private ProcessLogDao processLogDao;
	@PersistenceContext
	private EntityManager em;
	
	@Transactional(readOnly = false,rollbackFor=Exception.class)
	public void save(OccupiedAreaConfig oac)  throws Exception {
		OccupiedAreaConfig o = this.oacDao.findOne(oac.getId());
		if(o==null){
			throw new Exception("配置信息不存在!");
		}
		ProcessLog pl = new ProcessLog("OCCUPIED_AREA_CONFIG",oac.getWarehouseCode()+" 仓 "+oac.getCustomerCode()+"  添加了占用率配置. ("+"每 "+oac.getSkuNum()+"件SKU 占用面积 "+oac.getOccupiedArea() +" ㎡"+" 超出每平方 "+oac.getOverPrice()+" 原)");
		oac.setCreateBy(o.getCreateBy());
		oac.setCreateTime(o.getCreateTime());
		this.oacDao.save(oac);
		this.processLogDao.save(pl);
	}
	
	@Transactional(readOnly = false,rollbackFor=Exception.class)
	public void delete(long id) throws Exception {
		OccupiedAreaConfig oac = this.oacDao.findOne(id);
		if(oac==null){
			throw new Exception("配置信息不存在!");
		}
		ProcessLog pl = new ProcessLog("OCCUPIED_AREA_CONFIG","删除了"+oac.getWarehouseCode()+" 仓 "+oac.getCustomerCode()+" 占用率配置. ");
		this.oacDao.delete(id);
//		this.oacDao.delete(oac);
		this.processLogDao.save(pl);
	}
	
	@Transactional(readOnly = false,rollbackFor=Exception.class)
	public void saveBatch(List<OccupiedAreaConfig> oacList) {
		List<ProcessLog> plList = new ArrayList<ProcessLog>();
		ShiroUser user = (ShiroUser) SecurityUtils.getSubject().getPrincipal();
		for(int i = oacList.size() - 1 ; i >= 0 ;i-- ) {
			OccupiedAreaConfig oac = oacList.get(i);
			if(StringUtils.isEmpty(oac.getCustomerCode())&&
					StringUtils.isEmpty(oac.getWarehouseCode())){
				oacList.remove(i);
			}else {
				oac.setCreateBy(user.getJobuuid());
				oac.setCreateTime(new Date());
				
				ProcessLog pl = new ProcessLog("OCCUPIED_AREA_CONFIG",oac.getWarehouseCode()+" 仓 "+oac.getCustomerCode()+"  添加了占用率配置. ("+"每 "+oac.getSkuNum()+"件SKU 占用面积 "+oac.getOccupiedArea() +" ㎡"+" 超出每平方 "+oac.getOverPrice()+" 原)");
				plList.add(pl);
			}
		}
		this.oacDao.save(oacList);
		processLogDao.save(plList);
	}
	
	
	
	public OccupiedAreaConfig findById(Long id){
		return oacDao.findOne(id);
	}
	
	
	public Page<OccupiedAreaConfig> findBy(final String warehouseCode,final String customerCode,int pageNumber,int pageSize){
		Sort sort = new Sort(Direction.DESC, "customerCode","warehouseCode","createTime");
		PageRequest pageRequest = new PageRequest(pageNumber-1, pageSize,sort);
		return oacDao.findAll(new Specification<OccupiedAreaConfig>() {
			@Override
			public Predicate toPredicate(Root<OccupiedAreaConfig> r, CriteriaQuery<?> q, CriteriaBuilder cb) {
				Predicate pre = cb.conjunction();
				if (StringUtils.isNotBlank(warehouseCode)) {
					pre.getExpressions().add(cb.equal(r.<String> get("warehouseCode"), warehouseCode));
				}
				if (StringUtils.isNotBlank(customerCode)) {
					pre.getExpressions().add(cb.equal(r.<String> get("customerCode"), customerCode));
				}
				return pre;
			}

		},pageRequest);
	}
	
//	 currentArea;
//		private Double overArea;
	public List<OccupiedAreaResult> findByResult(final String warehouseCode,final String customerCode,String startTime,String endTime,int pageNumber,int pageSize){
//		StringBuffer sql = new StringBuffer("select * from (SELECT id, config_id,warehouse_code,customer_code,AVG(sku_num) sku_num,over_price,");
//		sql.append("occupied_area,assign_area,AVG(current_sku_num) current_sku_num,SUM(over_area) over_area,");
//		sql.append("SUM(over_total_price) over_total_price,create_time FROM w_occupied_area_result t  ");
//		sql.append("WHERE 1 =1 ");
		StringBuffer sql = new StringBuffer("select * from (SELECT  id, '' config_id,warehouse_code,customer_code,'' sku_num,'' over_price,");
		sql.append("'' occupied_area,'' assign_area,'' current_sku_num,'' current_area,'' stock_codes,sum(total_cost) total_cost,SUM(over_area) over_area,");
		sql.append("SUM(over_total_price) over_total_price,now() create_time FROM w_occupied_area_result t  ");
  		sql.append("WHERE 1 =1 ");
		if(StringUtils.isNotEmpty(warehouseCode)){
			sql.append("AND warehouse_code = ? ");
		}
		if(StringUtils.isNotEmpty(customerCode)){
			sql.append("AND customer_code = ? ");
		}
		if(StringUtils.isNotEmpty(startTime)){
			sql.append("AND create_time >= ? ");
		}
		if(StringUtils.isNotEmpty(endTime)){
			sql.append("AND create_time <= ? ");
		}
		sql.append(" GROUP BY config_id,customer_code,warehouse_code ORDER BY customer_code,warehouse_code ASC ");
		if(pageNumber != 0 && pageSize != 0){
			sql.append(" limit ?,? ");
		}
		sql.append(" ) z ");
		
		Query querySql = em.createNativeQuery(sql.toString(),OccupiedAreaResult.class);
		int index = 1;
		if(StringUtils.isNotEmpty(warehouseCode)){
			querySql.setParameter(index++, warehouseCode);
		}
		if(StringUtils.isNotEmpty(customerCode)){
			querySql.setParameter(index++, customerCode);
		}
		if(StringUtils.isNotEmpty(startTime)){
			querySql.setParameter(index++, startTime);
		}
		if(StringUtils.isNotEmpty(endTime)){
			querySql.setParameter(index++, endTime);
		}
		if(pageNumber != 0 && pageSize != 0){
			querySql.setParameter(index++, (pageNumber-1)*pageSize);
			querySql.setParameter(index++, pageSize);
		}
		@SuppressWarnings("unchecked")
		List<OccupiedAreaResult> idRs = querySql.getResultList();
		return idRs;
	}
	
	
	public BigInteger findByResultTotalNum(final String warehouseCode,final String customerCode,String startTime,String endTime,int pageNumber,int pageSize){
		StringBuffer sql = new StringBuffer("select count(1) totalNum from (SELECT 1 FROM w_occupied_area_result t ");
		sql.append("WHERE 1 =1 ");
		if(StringUtils.isNotEmpty(warehouseCode)){
			sql.append("AND warehouse_code = ? ");
		}
		if(StringUtils.isNotEmpty(customerCode)){
			sql.append("AND customer_code = ? ");
		}
		if(StringUtils.isNotEmpty(startTime)){
			sql.append("AND create_time > ? ");
		}
		if(StringUtils.isNotEmpty(endTime)){
			sql.append("AND create_time < ? ");
		}
		sql.append(" ORDER BY customer_code,warehouse_code ASC,create_time DESC ) z");
		
		
		Query querySql = em.createNativeQuery(sql.toString());
		int index = 1;
		if(StringUtils.isNotEmpty(warehouseCode)){
			querySql.setParameter(index++, warehouseCode);
		}
		if(StringUtils.isNotEmpty(customerCode)){
			querySql.setParameter(index++, customerCode);
		}
		if(StringUtils.isNotEmpty(startTime)){
			querySql.setParameter(index++, startTime);
		}
		if(StringUtils.isNotEmpty(endTime)){
			querySql.setParameter(index++, endTime);
		}
		List<BigInteger> totalNum = querySql.getResultList();
		
		return totalNum.get(0);
	}
	
	@Transactional(readOnly = true)
	public OccupiedAreaConfig findByCustomerCodeAndwarehouseCode(String customerCode,String warehouseCode){
		return  oacDao.findByCustomerCodeAndwarehouseCode(customerCode, warehouseCode);
	}
	
	/**************重算代码****************************/

	@Transactional(readOnly = false,rollbackFor=Exception.class)
	public void recountOccupiedAreaResult(String warehouseCode,String customerCode,String startTime,String endTime) throws Exception {
		OccupiedAreaConfig config = oacDao.findByCustomerCodeAndwarehouseCode(customerCode, warehouseCode);
		if(config==null){
			return;
		}
		//保存重算计划表
		OarRecountPlan orp = createRecountPlan(warehouseCode, customerCode, startTime, endTime);
		
		//安新配置重算库龄数据
		StringBuffer delAllSql = new StringBuffer("delete from w_occupied_area_result");
		delAllSql.append(" WHERE 1 =1 ");
		if(StringUtils.isNotEmpty(warehouseCode)){
			delAllSql.append("AND warehouse_code = ? ");
		}
		if(StringUtils.isNotEmpty(customerCode)){
			delAllSql.append("AND customer_code = ? ");
		}
		if(StringUtils.isNotEmpty(startTime)){
			delAllSql.append("AND create_time >= ? ");
		}
		if(StringUtils.isNotEmpty(endTime)){
			delAllSql.append("AND create_time <= ? ");
		}
		delAllSql.append("AND DAY(create_time)!=DAY(NOW()) ");
		Query delAllQuerySql = em.createNativeQuery(delAllSql.toString(),OccupiedAreaResult.class);
		int index = 1;
		if(StringUtils.isNotEmpty(warehouseCode)){
			delAllQuerySql.setParameter(index++, warehouseCode);
		}
		if(StringUtils.isNotEmpty(customerCode)){
			delAllQuerySql.setParameter(index++, customerCode);
		}
		if(StringUtils.isNotEmpty(startTime)){
			delAllQuerySql.setParameter(index++, startTime+" 00:00:00:000");
		}
		if(StringUtils.isNotEmpty(endTime)){
			delAllQuerySql.setParameter(index++, endTime+" 23:59:59:999");
		}
		delAllQuerySql.executeUpdate();
		
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		
		List<OccupiedAreaResult> resultList = new ArrayList<OccupiedAreaResult>();
		String opeTime = startTime;
		double loopCount = 0;
		double loopTotalCount = ((format.parse(endTime).getTime()-format.parse(startTime).getTime())/(24*60*60*1000));
		while(loopCount<loopTotalCount){
			loopCount++;
			//当天的不计算
			if(format.format(new Date()).equals(opeTime)||format.format(new Date()).equals(opeTime)){
				break;
			}
			//循环处理
			double currentStockNum = (double)getCurrentStockNum(customerCode,warehouseCode,opeTime);
			
			
			OccupiedAreaResult result = new OccupiedAreaResult();
			result.setCustomerCode(customerCode);
			result.setWarehouseCode(warehouseCode);
			result.setConfigId(config.getId());
			result.setSkuNum(config.getSkuNum());
			result.setOccupiedArea(config.getOccupiedArea());
			result.setAssignArea(config.getAssignArea());
			result.setOverPrice(config.getOverPrice());
			result.setCurrentSkuNum(currentStockNum);
			result.setCurrentArea((currentStockNum/config.getSkuNum())*config.getOccupiedArea());
			result.setOverArea(result.getCurrentArea()>0?(result.getCurrentArea()-config.getAssignArea()):0D);
			result.setOverTotalPrice(result.getOverArea()*config.getOverPrice());
			result.setCreateTime(format.parse(opeTime));
			//--------
			resultList.add(result);
			
			opeTime = format.format(new Date(format.parse(opeTime).getTime()+1*24*60*60*1000));
			double s = ((loopCount/loopTotalCount)*100);
			if(loopCount%5==0){
				orp.setProgress(s);
				orpService.save(orp);
			}
			
		}
		oarDao.save(resultList);
		
		orp.setProgress(100D);
		orp.setFinishTime(new Date());
		orp.setStatus("finish");
		orpService.save(orp);
	}
	
	
	@Transactional(readOnly = false,rollbackFor=Exception.class)
	public OarRecountPlan createRecountPlan(String warehouseCode,String customerCode,String startTime,String endTime) throws Exception {
		//盘点计划是否有重复计算区域
		List<OarRecountPlan> orpList = findRecountPlan(warehouseCode, customerCode,startTime,endTime);
		if(orpList.size()==0){
			ShiroUser user = (ShiroUser) SecurityUtils.getSubject().getPrincipal();
			OarRecountPlan orp = new OarRecountPlan();
			orp.setCustomerCode(customerCode);
			orp.setWarehouseCode(warehouseCode);
			orp.setStartTime(startTime);
			orp.setEndTime(endTime);
			orp.setRecountPerson(user.getJobuuid());
			orp.setCreateTime(new Date());
			orp.setStatus("doing");
			orpService.save(orp);
			return orp;
		}else{
			throw new Exception("already_exists");
		}
		
	}
	@Transactional(readOnly = false)
	public List<OarRecountPlan> findRecountPlan(String warehouseCode,String customerCode,String startTime,String endTime) throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM oar_recount_plan WHERE (STATUS = 'new' OR STATUS = 'doing' OR STATUS = 'error') ");
		if(StringUtils.isNotEmpty(warehouseCode)){
			sql.append("AND warehouse_code = ? ");
		}
		if(StringUtils.isNotEmpty(customerCode)){
			sql.append("AND customer_code = ? ");
		}
		if(StringUtils.isNotEmpty(startTime)&&StringUtils.isNotEmpty(endTime)){
			sql.append("AND start_time < ? ");
			sql.append("AND end_time > ? ");
		}
		Query querySql = em.createNativeQuery(sql.toString(),OarRecountPlan.class);
		int index = 1;
		if(StringUtils.isNotEmpty(warehouseCode)){
			querySql.setParameter(index++, warehouseCode);
		}
		if(StringUtils.isNotEmpty(customerCode)){
			querySql.setParameter(index++, customerCode);
		}
		if(StringUtils.isNotEmpty(startTime)&&StringUtils.isNotEmpty(endTime)){
			querySql.setParameter(index++, endTime);
			querySql.setParameter(index++, startTime);
		}
		List<OarRecountPlan> orpList = querySql.getResultList();
		return orpList;
		
	}
	
	@Transactional(readOnly = false)
	public List<OarRecountPlan> findRecountPlanLatelyForFinish() throws Exception {
		StringBuffer sql = new StringBuffer("SELECT * FROM oar_recount_plan WHERE (STATUS = 'finish') order by finish_time desc limit 0,1");
		Query querySql = em.createNativeQuery(sql.toString(),OarRecountPlan.class);
		List<OarRecountPlan> orpList = querySql.getResultList();
		return orpList;
		
	}
	
	public int getCurrentStockNum(String customerCode,String warehouseCode,String opeTime){
		int currentStockNum = 0;
		StringBuffer sql = new StringBuffer("SELECT sum(current_sku_num) FROM vw_customer_sku_total_num where warehouse_code is not null and ");
		sql.append("customer_code is not null ");
		if(StringUtils.isNotEmpty(warehouseCode)){
			sql.append("AND warehouse_code = ? ");
		}
		if(StringUtils.isNotEmpty(customerCode)){
			sql.append("AND customer_code = ? ");
		}
		Query querySql = em.createNativeQuery(sql.toString());
		int index = 1;
		if(StringUtils.isNotEmpty(warehouseCode)){
			querySql.setParameter(index++, warehouseCode);
		}
		if(StringUtils.isNotEmpty(customerCode)){
			querySql.setParameter(index++, customerCode);
		}
		List<BigDecimal> totalNum = querySql.getResultList();
		if(totalNum.size()==0){
			currentStockNum = 0;
		}else{
			currentStockNum = totalNum.get(0).intValue();
		}
		
		
		StringBuffer stockSql = new StringBuffer("SELECT SUM(num) FROM (");
		stockSql.append("SELECT customer_code,warehouse_code, -num num,ope_time FROM instorage_info where   warehouse_code IS NOT NULL AND customer_code IS NOT NULL ");
		
		if(StringUtils.isNotEmpty(warehouseCode)){
			stockSql.append("AND warehouse_code = ? ");
		}
		if(StringUtils.isNotEmpty(customerCode)){
			stockSql.append("AND customer_code = ? ");
		}
		if(StringUtils.isNotEmpty(opeTime)){
			stockSql.append("AND ope_time > ? ");
			stockSql.append("AND ope_time < now() ");
		}
		
		stockSql.append("UNION ALL ");
		stockSql.append("SELECT customer_code,warehouse_code,num ,ope_time FROM outstorage_info  where   warehouse_code IS NOT NULL AND customer_code IS NOT NULL ");
		
		if(StringUtils.isNotEmpty(warehouseCode)){
			stockSql.append("AND warehouse_code = ? ");
		}
		if(StringUtils.isNotEmpty(customerCode)){
			stockSql.append("AND customer_code = ? ");
		}
		if(StringUtils.isNotEmpty(opeTime)){
			stockSql.append("AND ope_time > ? ");
			stockSql.append("AND ope_time < now() ");
		}
		
		stockSql.append(") a WHERE 1 =1  ");
//		stockSql.append("and warehouse_code = ? and customer_code = ? and ope_time > ? and ope_time < ?");
		
		stockSql.append("GROUP BY customer_code,warehouse_code");
		Query queryStockSql = em.createNativeQuery(stockSql.toString());
		index = 1;
		if(StringUtils.isNotEmpty(warehouseCode)){
			queryStockSql.setParameter(1, warehouseCode);
			queryStockSql.setParameter(4, warehouseCode);
		}
		if(StringUtils.isNotEmpty(customerCode)){
			queryStockSql.setParameter(2, customerCode);
			queryStockSql.setParameter(5, customerCode);
		}
		if(StringUtils.isNotEmpty(opeTime)){
			queryStockSql.setParameter(3, opeTime+" 23:59:59:999");
			queryStockSql.setParameter(6, opeTime+" 23:59:59:999");
		}
		List<BigDecimal> stockTotalNum = queryStockSql.getResultList();
		if(stockTotalNum.size()==0){
			currentStockNum = currentStockNum+0;
		}else{
			currentStockNum = currentStockNum+stockTotalNum.get(0).intValue();
		}
		
		return currentStockNum;
	}

}